package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.ProductTypeDao;
import com.windfone.model.ProductType;

public class ProductTypeDaoImpl implements ProductTypeDao {
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}

	String SQL_DELETE_PRODUCT_TYPE=" DELETE FROM  producttype WHERE id=?";
	public int  deleteProductType(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_DELETE_PRODUCT_TYPE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_UPDATE_PRODUCT_TYPE=" UPDATE producttype SET typeName=? , langId=? ,typenameEnglish=?, imagepath=? WHERE id=?";

	public int updateProductType(final ProductType  productType) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_UPDATE_PRODUCT_TYPE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,productType.getTypeName());
						ps.setInt(i++,productType.getLangId());
						ps.setString(i++,productType.getTypenameEnglish());
						ps.setString(i++,productType.getImagepath());
						ps.setInt(i++,productType.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_INSERT_PRODUCT_TYPE=" INSERT INTO producttype (typeName,langId ,typenameEnglish,imagepath) VALUES( ?,?,?,? )  ";

	public int insertProductType(final ProductType productType) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_INSERT_PRODUCT_TYPE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,productType.getTypeName());
						ps.setInt(i++,productType.getLangId());
						ps.setString(i++,productType.getTypenameEnglish());
						ps.setString(i++,productType.getImagepath());						
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_GET_PRODUCT_TYPE_BYID=" SELECT * FROM  producttype  WHERE id=?";
	public   List<ProductType>  getProductTypeId(final int keyValue) {
		final List<ProductType> result = new ArrayList<ProductType>();
		try {
			jdbcTemplate.query(
				SQL_GET_PRODUCT_TYPE_BYID,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ProductType to= new ProductType();
 							to.setId(rs.getInt("id"));
 							to.setTypeName(rs.getString("typeName"));
 							to.setLangId(rs.getInt("langId"));
 							to.setTypenameEnglish(rs.getString("typenameEnglish"));
 							to.setImagepath(rs.getString("imagepath"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String SQL_GET_ALL_PRODUCT_TYPE=" SELECT * FROM  producttype  ORDER BY id ";
	public List<ProductType> getAllProductType( final int beginRecord) {
		final List<ProductType> result = new ArrayList<ProductType>();
		String sql = SQL_GET_ALL_PRODUCT_TYPE + " LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						ProductType to= new ProductType();
 							to.setId(rs.getInt("id"));
 							to.setTypeName(rs.getString("typeName"));
 							to.setLangId(rs.getInt("langId"));
 							to.setTypenameEnglish(rs.getString("typenameEnglish"));
 							to.setImagepath(rs.getString("imagepath")); 							
 							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	String SQL_GET_ACOUNT_PRODUCT_TYPE=" SELECT COUNT(1) FROM  producttype ";
	public int getAllRecorderNumber(  ) {
		final List<Integer> result = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
					SQL_GET_ACOUNT_PRODUCT_TYPE,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	String SQL_LIST_ALL_PRODUCT_TYPE=" SELECT * FROM  producttype  ORDER BY id ";
	public List<ProductType> listProductType() {
		final List<ProductType> result = new ArrayList<ProductType>();
		try {
			 jdbcTemplate.query(
					 SQL_LIST_ALL_PRODUCT_TYPE,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						ProductType to= new ProductType();
 							to.setId(rs.getInt("id"));
 							to.setTypeName(rs.getString("typeName"));
 							to.setLangId(rs.getInt("langId"));
 							to.setTypenameEnglish(rs.getString("typenameEnglish"));
 							to.setImagepath(rs.getString("imagepath")); 							
 							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String SQL_GET_PRODUCT_TYPE_BY_TYPENAME=" SELECT * FROM  producttype WHERE typeName=?";
	public ProductType getProductTypeBYTyepName(final String typeName) {
		final List<ProductType> result = new ArrayList<ProductType>();
		try {
			 jdbcTemplate.query(
				SQL_GET_PRODUCT_TYPE_BY_TYPENAME,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, typeName);
					}
				},				
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						ProductType to= new ProductType();
 							to.setId(rs.getInt("id"));
 							to.setTypeName(rs.getString("typeName"));
 							to.setLangId(rs.getInt("langId"));
 							to.setTypenameEnglish(rs.getString("typenameEnglish"));
 							to.setImagepath(rs.getString("imagepath")); 							
 							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result.get(0);
	}
}
